﻿////*************************************************************************************************************************************
////Created by: David Mello
////Date created: 08/06/2013
////Modified by: 
////Date modified: 
////Reason: 
////ULTI-104124 - As Peter, the payroll administrator, when I approve the W-2 print file for processing, the file format should respect the selected sort order. 
////(https://ultidev/browse/ULTI-104124)
////
////Number of tests: 4
////*************************************************************************************************************************************

namespace Echo.Automation.Payroll.W2_Print_Approval
{
    using System;
    using System.IO;
    using System.Linq;
    using Echo.Context.UltiProNet.Contexts;
    using Echo.Core.Common;
    using Echo.Core.TestAbstraction.WebUITest.MbUnit;
    using Echo.Models.UltimateSoftware.WebUITest.MbUnit;
    using MbUnit.Framework;

    [TestFixture]
    [Author("David Mello", "david_mello@ultimatesoftware.com")]
    [Category("W2 Print Approval")]
    [Release("10.7.2")]
    [Header("Config")]
    [Config(".NET")]
    public class W2PrintFileFormat : MbUnitAutomatedTest
    {
        UltiPro ultiPro;
        UltiProDatabaseContext database;

        private int lastYear;

        private const string TaxGroup = "WPT61";

        private string utfFilePath;

        [FixtureSetUp]
        public void FixtureSetUp()
        {
            this.ultiPro = Launch<UltiPro>();
            this.database = Init<UltiProDatabaseContext>();
            this.database.Company = ConfigDatabase("ULTIPRO_SB340");
            this.database.Site = ConfigDatabase("HRMS_SITE", Environment.UltiPro.SiteServer);

            this.database.Company.AddRollBack("paw2ApprovalDates");
            this.database.Company.AddRollBack("paw2Approvals");
            this.database.Company.AddRollBack("YRStatus");

            // Clear out any existing records.
            this.database.Company.Execute("DELETE FROM paw2ApprovalDates");
            this.database.Company.Execute(string.Format("DELETE FROM paw2approvals WHERE pawTaxGroup = '{0}'", TaxGroup));
            lastYear = DateTime.Now.AddYears(-1).Year;

            this.RemoveExistingUtfFiles();

            // Login and click Reporting > W2 Print Approval.
            this.ultiPro.Pages.Login.LoginAs("BlackM", "password", Config.LanguageCode);
        }

        [FixtureTearDown]
        public void FixtureTearDown()
        {
            this.ultiPro.Browser.Quit();
        }

        [SetUp]
        public void TestSetup()
        {
            // Insert record into paw2ApprovalDates to enable print approval.
            this.database.Company.Execute(string.Format(
                "INSERT INTO paw2ApprovalDates (paaFinalApprovalDate, paaYear) VALUES ('{0}',{1})",
                DateTime.Now.AddDays(1).ToShortDateString(), lastYear));

            // Flip all the existing YRStatus records to READY state.
            this.database.Company.Execute(string.Format("UPDATE YRStatus SET yrsStatus = 'READY', yrsDistributed = 'N' WHERE yrsTaxCalcGroupID = '{0}'", TaxGroup));
        }

        [TearDown]
        public void TestTearDown()
        {
            this.database.Company.RollBackDatabase();

            // Move the print file created during the test to the archive directory.
            File.Move(utfFilePath, utfFilePath.Replace("w2print", "w2print\\archive"));
        }

        /// <summary> Requirements Tested
        /// ULTI-104124 - As Peter, the payroll administrator, when I approve the W-2 print file for processing, the file format should respect the selected sort order.
        /// This test selects the "PayGroup, Last Name, First Name" sort option and verifies that in the print file character locations 450-499 contain the sort
        /// order the user selected.  The test looks up what the expected pay group should be by checking the SSN on the RM row against the row in the YREESUM table
        /// matching the employee SSN.
        /// </summary>
        [Test]
        public void PrintFilePersistsPayGroupLnFnSortOrder()
        {
            // Given I have approved my tax group using the "PayGroup, Last Name, First Name" sort order.
            this.ApproveW2ForPrintingWithSortOption(TaxGroup, "PayGroup, Last Name, First Name");

            // When the W2ApproveAndSubmitJob runs.
            this.ExecuteW2ApproveAndSubmitJobAndWaitForCompletion();

            // Then the W2 Print File will have the selected sort order for each employee.
            // Locations 3 to 11 in the print file contain the SSN.  Locations 449 to 499 contain the sort value.  Parsing this out below.
            utfFilePath = this.ReturnUtfFileLocation();
            Console.WriteLine("UTF File Location for test PrintFilePersistsPayGroupLnFnSortOrder: {0}", utfFilePath);
            var rmLines = File.ReadAllLines(utfFilePath)
                .Where(s => s.StartsWith("RM")).Select(x => x.Substring(2, 9) + x.Substring(449, 50).Trim());

            foreach (var rmLine in rmLines)
            {
                Assert.AreEqual(
                    this.ReturnPayGroupForSsn(rmLine.Substring(0, 9)), 
                    rmLine.Substring(9, rmLine.Length - 9));
            }
        }

        /// <summary> Requirements Tested
        /// ULTI-104124 - As Peter, the payroll administrator, when I approve the W-2 print file for processing, the file format should respect the selected sort order.
        /// This test selects the "Last Name, First Name" sort option.  An RM record in the print file will not be created if the default LastName, FirstName sort
        /// option is selected unless there is other applicable RM data for the employee.  In this case there is none so the test verifies 0 RM rows are in the file.
        /// </summary>
        [Test]
        public void PrintFilePersistsLnFnSortOrder()
        {
            // Given I have approved my tax group using the "Last Name, First Name" sort order.
            this.ApproveW2ForPrintingWithSortOption(TaxGroup, "Last Name, First Name");

            // When the W2ApproveAndSubmitJob runs.
            this.ExecuteW2ApproveAndSubmitJobAndWaitForCompletion();

            // Then there will be no RM records in the W2 print file because LastName, FirstName is assumed and there is no other applicable RM row data.
            utfFilePath = this.ReturnUtfFileLocation();
            var rmLines = File.ReadAllLines(utfFilePath)
                .Where(s => s.StartsWith("RM")).Select(x => x.Substring(2, 9) + x.Substring(449, 50));
            Assert.AreEqual(0, rmLines.Count());
        }

        /// <summary> Requirements Tested
        /// ULTI-104124 - As Peter, the payroll administrator, when I approve the W-2 print file for processing, the file format should respect the selected sort order.
        /// This test selects the "Location, Last Name, First Name" sort option and verifies that in the print file character locations 450-499 contain the sort
        /// order the user selected.  The test looks up what the expected location should be by checking the SSN on the RM row against the row in the YREESUM table
        /// matching the employee SSN.
        /// </summary>
        [Test]
        public void PrintFilePersistsLocationLnFnSortOrder()
        {
            // Given I have approved my tax group using the "Location, Last Name, First Name" sort order.
            this.ApproveW2ForPrintingWithSortOption(TaxGroup, "Location, Last Name, First Name");

            // When the W2ApproveAndSubmitJob runs.
            this.ExecuteW2ApproveAndSubmitJobAndWaitForCompletion();

            // Then the W2 Print File will have the selected sort order for each employee.
            utfFilePath = this.ReturnUtfFileLocation();
            var rmLines = File.ReadAllLines(utfFilePath)
                .Where(s => s.StartsWith("RM")).Select(x => x.Substring(2, 9) + x.Substring(449, 50).Trim());

            foreach (var rmLine in rmLines)
            {
                Assert.AreEqual(
                    this.ReturnLocationForSsn(rmLine.Substring(0, 9)).Trim(),
                    rmLine.Substring(9, rmLine.Length - 9));
            }
        }

        /// <summary> Requirements Tested
        /// ULTI-104124 - As Peter, the payroll administrator, when I approve the W-2 print file for processing, the file format should respect the selected sort order.
        /// This test selects the "Region, Division, Department, Last Name, First Name" sort option and verifies that in the print file character locations 450-499 contain the sort
        /// order the user selected.  The test looks up what the expected region, division, and department should be by checking the SSN on the RM row against the row in the YREESUM table
        /// matching the employee SSN.
        /// </summary>
        [Test]
        public void PrintFilePersistsRegionDivisionDeptLnFnSortOrder()
        {
            // Given I have approved my tax group using the "Region, Division, Department, Last Name, First Name" sort order.
            this.ApproveW2ForPrintingWithSortOption(TaxGroup, "Region, Division, Department, Last Name, First Name");

            // When the W2ApproveAndSubmitJob runs.
            this.ExecuteW2ApproveAndSubmitJobAndWaitForCompletion();

            // Then the W2 Print File will have the selected sort order for each employee.
            utfFilePath = this.ReturnUtfFileLocation();
            Console.WriteLine("UTF File Location for test PrintFilePersistsRegionDivisionDeptLnFnSortOrder: {0}", utfFilePath);
            var rmLines = File.ReadAllLines(utfFilePath)
                .Where(s => s.StartsWith("RM")).Select(x => x.Substring(2, 9) + x.Substring(449, 50));

            foreach (var rmLine in rmLines)
            {
                Assert.AreEqual(
                    this.ReturnRegionDivisionDeptForSsn(rmLine.Substring(0, 9)),
                    rmLine.Substring(9, rmLine.Length - 9));
            }
        }

        #region Helper methods

        /// <summary>
        /// Gets the full UTF file path and name from the UES server.
        /// </summary>
        /// <returns>UTF filepath.</returns>
        private string ReturnUtfFileLocation()
        {
            // Printing for debug purposes.
            Console.WriteLine(string.Format(@"\\{0}\c$\temp\w2print", Environment.UltiPro.UesServer) + " contains files:");
            var fileNames = Directory.EnumerateFiles(
                string.Format(@"\\{0}\c$\temp\w2print", Environment.UltiPro.UesServer),
                "USG1000_LASB_UTFSW2PRNT_*.UTF");
            foreach (var fileName in fileNames)
            {
                Console.WriteLine(fileName);
            }

            return Directory.EnumerateFiles(
                string.Format(@"\\{0}\c$\temp\w2print", Environment.UltiPro.UesServer),
                string.Format("USG1000_LASB_UTFSW2PRNT_{0}_{1}_{2}_{3}*.UTF", TaxGroup, lastYear, DateTime.Now.ToString("yyyyMMdd"), DateTime.Now.ToString("HH"))).First();
        }

        private void RemoveExistingUtfFiles()
        {
            var filePath = string.Format(@"\\{0}\c$\temp\w2print", Environment.UltiPro.UesServer);

            if (!Directory.Exists(filePath)) return;

            var fileNames = Directory.EnumerateFiles(
                filePath,
                "USG1000_LASB_UTFSW2PRNT_*.UTF");

            foreach (var fileName in fileNames)
            {
                File.Move(fileName, fileName.Replace("w2print", "w2print\\archive"));
            }
        }

        /// <summary>
        /// Returns the pay group for a given SSN.
        /// </summary>
        /// <param name="ssn">SSN to search on.</param>
        /// <returns>Employee pay group.</returns>
        private string ReturnPayGroupForSsn(string ssn)
        {
            var query =
                string.Format(
                @"
                SELECT  YesPayGroup
                FROM YREESum
                WHERE YesTaxYear = {0}
                AND YesTaxCalcGroupID = '{1}'
                AND YesSSN = '{2}'
                ORDER BY YesPayGroup, YesNameLast, YesNameFirst", lastYear, TaxGroup, ssn);

            return this.database.Company.GetValue(query);
        }

        /// <summary>
        /// Returns the location for an employee using their SSN.
        /// </summary>
        /// <param name="ssn">The SSN of the employee.</param>
        /// <returns>The employee's location.</returns>
        private string ReturnLocationForSsn(string ssn)
        {
            var query =
                string.Format(
                @"
                SELECT  YesLocation
                FROM YREESum
                WHERE YesTaxYear = {0}
                AND YesTaxCalcGroupID = '{1}'
                AND YesSSN = '{2}'
                ORDER BY YesPayGroup, YesNameLast, YesNameFirst", lastYear, TaxGroup, ssn);

            return this.database.Company.GetValue(query);
        }

        /// <summary>
        /// Returns the region, division, and department for an employee using their SSN.  This concats and pads the columns to match the print file specification.
        /// </summary>
        /// <param name="ssn">The SSN of the employee.</param>
        /// <returns>The employee's padded region, division, and department.</returns>
        private string ReturnRegionDivisionDeptForSsn(string ssn)
        {
            var query =
                string.Format(
                @"
                SELECT  YesOrgLvl1, YesOrgLvl2, YesOrgLvl3
                FROM YREESum
                WHERE YesTaxYear = {0}
                AND YesTaxCalcGroupID = '{1}'
                AND YesSSN = '{2}'
                ORDER BY YesPayGroup, YesNameLast, YesNameFirst", lastYear, TaxGroup, ssn);

            var row = this.database.Company.GetRow(query);

            return string.Concat(
                row["YesOrgLvl1"].ToString().PadRight(10, ' '),
                row["YesOrgLvl2"].ToString().PadRight(10, ' '),
                row["YesOrgLvl3"].ToString().PadRight(30, ' '));
        }

        /// <summary>
        /// This executes the W2 approve and submit job and waits for it to complete.
        /// </summary>
        private void ExecuteW2ApproveAndSubmitJobAndWaitForCompletion()
        {
            var messageIdBeforeJobRun = this.database.Site.GetValue(
                @"
                USE HRMS_DISPATCH
                SELECT TOP 1 msg.MessageID
                FROM msgmessage msg 
                LEFT JOIN msgqueuebatchmessage mqbm ON msg.messageID = mqbm.messageID 
                LEFT JOIN msgqueuebatch mqb ON mqbm.batchid = mqb.batchid
                WHERE DateDiff(d, msg.insertdate, GETDATE()) = 0
                ORDER BY msg.InsertDate DESC");

            this.database.Company.Execute("EXEC msdb..sp_start_job 'W2ApproveAndSubmitJob'");

            // Pause for the batch messages to generate.
            var count = 0;

            const string W2Messagequery = @"
                USE HRMS_DISPATCH
                SELECT TOP 1 msg.MessageID
                FROM msgmessage msg 
                LEFT JOIN msgqueuebatchmessage mqbm ON msg.messageID = mqbm.messageID 
                LEFT JOIN msgqueuebatch mqb ON mqbm.batchid = mqb.batchid
                WHERE DateDiff(d, msg.insertdate, GETDATE()) = 0
                AND msg.Message LIKE '<W2%' AND MessageCurrent IS NULL
                ORDER BY msg.InsertDate DESC";

            var lastMessageId = this.database.Site.GetValue(W2Messagequery);
            
            while ((string.IsNullOrEmpty(lastMessageId) || lastMessageId == messageIdBeforeJobRun) && count < 12)
            {
                lastMessageId = this.database.Site.GetValue(W2Messagequery);

                System.Threading.Thread.Sleep(5000);
                count++;
            }

            // Exit if there were no dispatch records found.
            if (string.IsNullOrEmpty(lastMessageId))
            {
                Assert.Fail("HRMS_DISPATCH returned no W2 dispatch records.");
                return;
            }

            count = 0;

            // Job can take up to 10 minutes to process.
            while (count < 20 && string.IsNullOrEmpty(
                this.database.Site.GetValue(string.Format("select messagecurrent FROM HRMS_DISPATCH..msgqueuebatchmessage where messageid = {0}", lastMessageId))))
            {
                System.Threading.Thread.Sleep(30000);
                count++;
            }
        }

        /// <summary>
        /// Approves a tax group for W2 print through the UI.
        /// </summary>
        /// <param name="taxGroup">The tax group to approve.</param>
        /// <param name="sortOption">Sort option drop down options {"Last Name, First Name", "PayGroup, Last Name, First Name", "Location, Last Name, First Name", "Region, Division, Department, Last Name, First Name"}</param>
        private void ApproveW2ForPrintingWithSortOption(string taxGroup, string sortOption)
        {
            // Navigate to Reporting and open the tax group.
            this.ultiPro.Menu.Reporting.Click(link => link.W2PrintApproval);
            this.ultiPro.Pages.W2PrintApprovalSummary.W2PrintApprovalSummaryGrid.Row(row => row.TaxGroupLink, taxGroup).Click(link => link.TaxGroupLink);

            // Change the sort option.
            this.ultiPro.Pages.W2PrintApprovalDetail.Set(select => select.SortOptionsDropDown, sortOption, SelectOption.Text);

            // Fill in the required fields.
            this.ultiPro.Pages.W2PrintApprovalDetail.Set(
                x => x.PrimaryEmailAddressTextBox, "w2@preferencetesting.org");
            this.ultiPro.Pages.W2PrintApprovalDetail.Set(x => x.CopyRecipientsDropDown, "ARTRIPL1", SelectOption.Value);

            // Click the approval checkbox and save.
            this.ultiPro.Pages.W2PrintApprovalDetail.Click(x => x.PrintApprovalStatusCheckBox);
            this.ultiPro.Pages.W2PrintApprovalDetail.ButtonBar.Click(b => b.Save);

            // Confirm changes.
            this.ultiPro.Pages.W2PrintApprovalDetail.ConfirmBox.Click(x => x.CheckboxConfirm);
            this.ultiPro.Pages.W2PrintApprovalDetail.ConfirmBox.Click(x => x.YesButton);
        }
        #endregion
    }
}
